/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package cassandrawindow;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class SqlServerDataManager {
    private Connection conn = null;

    private String ViewForAdmicro           = "ViewFor"
            + "Admicro";
    private String NewsFeaturedRecomment    = "NewsFeaturedRecomment";
    public SqlServerDataManager()
    {
        try
        {
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
            conn = DriverManager.getConnection(ACCOUNT.URL_SQLSERVER , ACCOUNT.username_sqlserver, ACCOUNT.pass_sqlserver);//abc!@#
            System.out.println("connected");
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
    }

    public List getNewsUrlByDate(String date)
    {
        List infor = new ArrayList();
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("select NewsUrl from "+ ViewForAdmicro +" where CONVERT(VARCHAR(10), PublishDate, 120) = '"+ date+"'");
            Debug.println("select NewsUrl from "+ ViewForAdmicro+" where CONVERT(VARCHAR(10), PublishDate, 120) = "+date);
            while (rs.next())
            {
                infor.add(rs.getString("NewsUrl"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            System.out.println(ex.getMessage());
        }
        return infor;
    }

    public int getCountHotNewsCoverPage()
    {
        int relVal = 0;
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT COUNT(*) from NewsFeaturedRecomment where GeneralCatID = -1");
            
            if (rs.next())
            {
                relVal ++;
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            System.out.println(ex.getMessage());
        }
        return relVal;

    }

    public boolean deltHotNewsCoverPage()
    {
        boolean relVal = false;
        try
        {
            Statement st= (Statement)conn.createStatement();
            String sql = "DELETE from NewsFeaturedRecomment where GeneralCatID = -1";
            relVal = st.execute(sql);
            relVal = true;
            st.close();
        }
        catch (Exception ex)
        {
            System.out.println(ex.getMessage());
        }
        return relVal;
    }

    public boolean insertHotNewCoverPage(String value)
    {
        boolean retValue = false;
        try {
            Statement s = conn.createStatement();
            String sql = "insert into "+ NewsFeaturedRecomment +"(NewsId, Source, Type, Avatar, FK_NewsId, CreatedDate, GeneralCatID,Rank,IsSelected) values "+ value;
            //Debug.println(sql);
            retValue = s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
        }
        return retValue;
    }

    public String getAllValueHotNewCoverPageByNewsId(long NewsId,int Rank)
    {
        String retVal = "";
        StringBuilder sb = new StringBuilder();
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("select a.Source,b.Avatar,a.GeneralCatID,a.Type,a.CreatedDate,b.Id From NewsFeaturedRecomment a join ViewForAdmicro b on a.fk_newsid=b.id where b.NewsID = "+NewsId);
            while (rs.next())
            {
                sb.append("(").append(NewsId).append(",").
                append(rs.getInt("Source")).append(",").append(rs.getInt("Type")).append(",").append("'").append(rs.getString("Avatar")).append("'").
                append(",").append(rs.getInt("Id")).append(",").append("'").append(rs.getString("CreatedDate")).append("'").append(",").append(-1).append(",").append(Rank).append(",").
                                                                                append(0).append(")");
            break;
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            System.out.println(ex.getMessage());
        }

        return sb.toString();
    }

    public boolean checkExistByNewsId(long NewsId)
    {
        boolean retVal = false;
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("select * From NewsFeaturedRecomment where NewsID = "+ NewsId +" and GeneralCatID = -1");
            while (rs.next())
            {
                retVal = true;
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            System.out.println(ex.getMessage());
        }

        return retVal;
    }

    public boolean updateRankByNewsId(long NewsId,int Rank)
    {
        boolean retVal = false;
        try
        {
            Statement st= (Statement)conn.createStatement();
            int update_count = st.executeUpdate("UPDATE NewsFeaturedRecomment SET Rank= '"+ Rank + "'WHERE NewsID = "+ NewsId);
            if(update_count == 1)
            {
                retVal = true;
            }
        }
        catch (Exception e) {
        }
        return retVal;
    }

    public boolean checkIsSelectedByNewsId(long NewsId)
    {
        boolean retVal = false;
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("select * from NewsFeaturedRecomment where Type = 2 and GeneralCatID = -1");
            while (rs.next())
            {
                retVal = true;
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            System.out.println(ex.getMessage());
        }

        return retVal;

    }
}
